﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public partial class Message
    {
        DbHelperSQL dbhelper = new DbHelperSQL();


        #region 根据MId执行该留言或回复已经看过
        /// <summary>
        /// 根据MId执行该留言或回复已经看过
        /// </summary>
        /// <param name="mid"></param>
        /// <returns></returns>
        public bool isLookByMid(int mid)
        {
            string sql = "update [Message] set MIsLook=1 where MId=@MId;";
            SqlParameter[] paras = { 
                                      new SqlParameter("@MId",mid)
                                   };
            int r = dbhelper.ExecuteCommand(sql, paras);
            return r > 0;
        } 
        #endregion

        #region 根据MId执行软删除  删除留言 回复
        /// <summary>
        /// 根据MId执行软删除  删除留言 回复
        /// </summary>
        /// <param name="mid"></param>
        /// <returns></returns>
        public bool softDelByMid(int mid)
        {
            string sql = "update [Message] set MIsDel=1 where MId=@MId;";
            SqlParameter[] paras = { 
                                      new SqlParameter("@MId",mid)
                                   };
            int r = dbhelper.ExecuteCommand(sql, paras);
            return r > 0;
        } 
        #endregion

        #region 根据用户UId(MPFromUid)获取留言数量
        /// <summary>
        /// 根据用户UId(MPFromUid)获取留言数量，返回实体对象集合
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public int getMCByuid(int uid)
        {
            string sql = "select a.num1+b.num2 from"+
                         " (select count(*)num1 from [Message]"+
                         " where MPFromUId=@MPFromUId" +
                         " and MIsLook=0 and MPFromUId!=MFromUId and MIsDel=0)a," +
                         " (select count(*) num2 from [Message]"+ 
                         " m join LostAndFound laf on m.MLId=laf.LId"+
                         " where laf.UserId=@MPFromUId and IsDel=0 and MIsDel=0 and MIsLook=0 and laf.UserId!=m.MFromUId)b";
            SqlParameter[] paras = { 
                                      new SqlParameter("@MPFromUId",uid)
                                   };
            int r = dbhelper.GetScalar(sql, paras);
            return r;
        } 
        #endregion

        #region 获取留言内容(根据MLId 针对某一个具体的启事)
        /// <summary>
        /// 获取留言内容
        /// </summary>
        /// <param name="mtouid"></param>
        /// <returns></returns>
        public List<MODEL.Message> getMessage(int mlid)
        {
            string sql = "select * from [Message] m join Users u on m.MFromUId=u.UId and m.MLId=@MLId order by MAddTime desc ";
            SqlParameter[] paras = { 
                                      new SqlParameter("@MLId",mlid)
                                   };
            List<MODEL.Message> list = new List<MODEL.Message>();
            DataTable dt = dbhelper.GetDataTable(sql, paras);
            if (dt == null)
            {
                return null;
            }
            else
            {
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.Message model = new MODEL.Message();
                    setDr2Model(dr, model);
                    list.Add(model);
                }
                return list;
            }
        }
        
        #endregion

        #region 获取留言内容（根据用户id获取全部启事的留言内容(未看过））
        /// <summary>
        /// 获取留言内容（根据用户id获取全部启事的留言内容）
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<MODEL.Message> getAllMessage(int uid)
        {
            string sql = "select * from [Message] m,LostAndFound laf,Users u"+
                          "  where m.MLId=laf.LId  and m.MFromUId=u.UId"+
                          "  and laf.UserId=@UserId and IsDel=0 and MIsDel=0 and MIsLook=0 and laf.UserId!=m.MFromUId order by MAddTime desc ";
            SqlParameter[] paras = { 
                                      new SqlParameter("@UserId",uid)
                                   };
            List<MODEL.Message> list = new List<MODEL.Message>();
            DataTable dt = dbhelper.GetDataTable(sql, paras);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.Message model = new MODEL.Message();
                    setDr2Model(dr, model);
                    list.Add(model);
                }
                return list;
            }
        } 
        #endregion

        #region 获取留言内容（根据用户id获取全部启事的留言内容）
        /// <summary>
        /// 获取留言内容（根据用户id获取全部启事的留言内容）
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<MODEL.Message> getAllMessage2(int uid)
        {
            string sql = "select * from [Message] m,LostAndFound laf,Users u" +
                          "  where m.MLId=laf.LId  and m.MFromUId=u.UId" +
                          "  and laf.UserId=@UserId and IsDel=0 and MIsDel=0 order by MAddTime desc";
            SqlParameter[] paras = { 
                                      new SqlParameter("@UserId",uid)
                                   };
            List<MODEL.Message> list = new List<MODEL.Message>();
            DataTable dt = dbhelper.GetDataTable(sql, paras);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.Message model = new MODEL.Message();
                    setDr2Model(dr, model);
                    list.Add(model);
                }
                return list;
            }
        }
        #endregion

        #region 根据用户id获得回复的内容 （未看过）
        /// <summary>
        /// 根据用户id获得回复的内容 (未看过）
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<MODEL.Message> getReply(int uid)
        {
            string sql = "select * from [Message] m,LostAndFound laf,Users u"+
                          "  where MPFromUId=@MPFromUId" +
                          "  and MIsLook=0 and MPFromUId!=MFromUId"+
                          "  and m.MLId=laf.LId and m.MFromUId=u.UId order by MAddTime desc";
            SqlParameter[] paras = { 
                                      new SqlParameter("@MPFromUId",uid)
                                   };
            List<MODEL.Message> list = new List<MODEL.Message>();
            DataTable dt = dbhelper.GetDataTable(sql, paras);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.Message model = new MODEL.Message();
                    setDr2Model(dr, model);
                    list.Add(model);
                }
                return list;
            }
        }
        #endregion

        #region 根据用户id获得回复的内容 
        /// <summary>
        /// 根据用户id获得回复的内容 
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<MODEL.Message> getReply2(int uid)
        {
            string sql = "select * from [Message] m,LostAndFound laf,Users u" +
                          "  where MPFromUId=@MPFromUId" +
                          "  and MPFromUId!=MFromUId and MIsDel=0 " +
                          "  and m.MLId=laf.LId and m.MFromUId=u.UId order by MAddTime desc";
            SqlParameter[] paras = { 
                                      new SqlParameter("@MPFromUId",uid)
                                   };
            List<MODEL.Message> list = new List<MODEL.Message>();
            DataTable dt = dbhelper.GetDataTable(sql, paras);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.Message model = new MODEL.Message();
                    setDr2Model(dr, model);
                    list.Add(model);
                }
                return list;
            }
        }
        #endregion

        #region 留言，向数据库中插入相应的留言数据
        /// <summary>
        /// 留言，向数据库中插入相应的留言数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool takeAMessage(MODEL.Message model)
        {
            string sql = "insert into [Message](MLId,MFromUId,MPFromUId,MContent)values(@MLId,@MFromUId,@MPFromUId,@MContent);";
            SqlParameter[] paras = { 
                                      new SqlParameter("@MLId",model.MLId),
                                      new SqlParameter("@MFromUId",model.MFromUId),
                                      new SqlParameter("@MPFromUId",model.MPFromUId),
                                      new SqlParameter("@MContent",model.MContent)
                                   };
            int r = dbhelper.ExecuteCommand(sql, paras);
            return r > 0;
        } 
        #endregion

        #region 将数据行转换成实体对象
        /// <summary>
        /// 将数据行转换成实体对象
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="model"></param>
        public void setDr2Model(DataRow dr, MODEL.Message model)
        {
            if (dr.Table.Columns.Contains("MId") && !string.IsNullOrEmpty(dr["MId"].ToString()))
            {
                model.MId = Convert.ToInt32(dr["MId"]);
            }
            if (dr.Table.Columns.Contains("MLId") && !string.IsNullOrEmpty(dr["MLId"].ToString()))
            {
                model.MLId = Convert.ToInt32(dr["MLId"]);
                MODEL.LostAndFound laf = new MODEL.LostAndFound();
                new DAL.LostAndFound().setDr2Model(dr, laf);
                model.Mlaf= laf;
            }
            if (dr.Table.Columns.Contains("MFromUId") && !string.IsNullOrEmpty(dr["MFromUId"].ToString()))
            {
                model.MFromUId = Convert.ToInt32(dr["MFromUId"]);
                MODEL.Users user = new MODEL.Users();
                new DAL.Users().setDr2Model(dr, user);
                model.Mfromuser = user;
            }
            if (dr.Table.Columns.Contains("MPFromUId") && !string.IsNullOrEmpty(dr["MPFromUId"].ToString()))
            {
                model.MPFromUId = Convert.ToInt32(dr["MPFromUId"]);
                MODEL.Users user = new MODEL.Users();
                new DAL.Users().setDr2Model(dr, user);
                model.Mpfromuser = user;
            }
            if (dr.Table.Columns.Contains("MContent") && !string.IsNullOrEmpty(dr["MContent"].ToString()))
            {
                model.MContent = dr["MContent"].ToString();
            }
            if (dr.Table.Columns.Contains("MIsDel") && !string.IsNullOrEmpty(dr["MIsDel"].ToString()))
            {
                model.MIsDel = Convert.ToBoolean(dr["MIsDel"]);
            }
            if (dr.Table.Columns.Contains("MAddTime") && !string.IsNullOrEmpty(dr["MAddTime"].ToString()))
            {
                model.MAddTime = Convert.ToDateTime(dr["MAddTime"]);
            }
            if (dr.Table.Columns.Contains("MIsLook") && !string.IsNullOrEmpty(dr["MIsLook"].ToString()))
            {
                model.MIsLook = Convert.ToBoolean(dr["MIsLook"]);
            }
        } 
        #endregion
    }
}
